package Database;

import java.sql.Connection;
import java.util.ArrayList;

import javax.swing.*;

import Exception.*;
import General.*;

public class AccountMgmt
{
	private Connection connection;
	private CallDB callDB;

	public AccountMgmt(Connection connection) throws LoadDriverException
	{
		this.connection = connection;
		callDB = new CallDB();
	}

	/**
	 * Recupere des informations sur les comptes existants dans la base de donnees.
	 * SELECT * FROM account
	 * @return
	 * MyTableModel reprenant les logins, prenoms et noms des comptes existants
	 * @throws CheckDataException
	 */
	// Request - Ok !
	public MyTableModel selectAllElements() throws CheckDataException
	{
		MyTableModel resultModel;
		
		// SELECT firstname, lastname, id FROM account WHERE id != 7;
		resultModel = callDB.checkData(connection, "SELECT firstname, lastname, id FROM account WHERE id != " + GeneralMethod.getSession().getId() + ";");
		
		return resultModel;
	}
	
	public MyTableModel selectOneElement(int idAccount) throws CheckDataException
	{		
		MyTableModel resultModel = callDB.checkData(connection, "SELECT id, login, password, manager, firstname, lastname FROM account WHERE id = " + idAccount + ";");
		
		return resultModel;
	}
	
	public MyTableModel checkLogin(String login) throws CheckDataException
	{
		MyTableModel resultModel = callDB.checkData(connection, "SELECT id FROM account WHERE login = \'" + login + "\';");
		
		return resultModel;
	}
	
	/**
	 * Recupere les cours associes au compte.
	 * @param idAccount
	 * L'id du compte selectionne
	 * @return
	 * MyTableModel reprenant les cours auquel est lie le compte.
	 * @throws CheckDataException
	 */
	// Request - Ok !
	public MyTableModel selectBoundCourses(int idAccount) throws CheckDataException
	{
		//SELECT course.libelle, course.id FROM account, account_course, course WHERE account_course.fk_account = account.id AND account_course.fk_course = course.id AND account.id = 7;
		MyTableModel resultModel = callDB.checkData(connection,	"SELECT course.libelle, course.id " +
																"FROM account, account_course, course " +
																"WHERE account_course.fk_account = account.id " +
																	"AND account_course.fk_course = course.id " +
																	"AND account.id = " + idAccount + ";");
		return resultModel;
	}
	
	public MyTableModel selectBoundClasses(int idAccount) throws CheckDataException
	{
		// SELECT DISTINCT(classe.yearcode), classe.sectioncode, classe.groupletter FROM account, account_course, course, classe_course, classe WHERE account_course.fk_account = account.id AND account_course.fk_course = course.id AND classe_course.fk_course = course.id AND classe_course.fk_classe = classe.id AND account.id = 7 ORDER BY classe.id;
		MyTableModel resultModel = callDB.checkData(connection,	"SELECT DISTINCT(classe.yearcode), classe.sectioncode, classe.groupletter " +
																"FROM account, account_course, course, classe_course, classe " +
																"WHERE account_course.fk_account = account.id " +
																	"AND account_course.fk_course = course.id " +
																	"AND classe_course.fk_course = course.id " +
																	"AND classe_course.fk_classe = classe.id " +
																	"AND account.id = " + idAccount + " ORDER BY classe.id;");
		return resultModel;
	}
	
	/**
	 * Rajoute un nouveau compte dans la base de donnees.
	 * @param login
	 * Le login du compte a rajouter
	 * @param password
	 * Le mot de passe du compte a rajouter
	 * @param manager
	 * Le grade du compte (0=Teacher, 1=Manager)
	 * @param firstName
	 * Le prenom du compte a rajouter
	 * @param lastName
	 * Le nom du compte a rajouter
	 * @param tabIdCourse 
	 * Un tableau d'Integer reprenant les id des cours associees au compte
	 * @return
	 * Le nombre de lignes modifiees
	 * @throws InsertionDataException, CheckDataException
	 * @throws LoadDriverException 
	 */
	// Requests - Ok !
	public int createElement(String login, String password, int manager, String firstName, String lastName, ArrayList<Integer> arrayIdCourse) throws InsertionDataException, CheckDataException, LoadDriverException
	{
		int nbModifiedRows = 0;
		
		// Creation du compte
		// INSERT INTO account (login,password,manager,firstname,lastname) VALUES ('Clute', 'Tigrou007', 0, 'Clu', 'Te');
		nbModifiedRows += callDB.insertData(connection, "INSERT INTO account (login,password,manager,firstname,lastname) " +
														"VALUES (\'" + login + "\',\'" + password + "\'," + manager + ",\'" + firstName + "\',\'" + lastName + "\');");
		
		// Recuperation de l'id du compte nouvellement cree
		// SELECT id FROM account WHERE login='Clute';		=>	id = 11
		MyTableModel resultModel = callDB.checkData(connection, "SELECT id FROM account WHERE login=\'" + login + "\';");
		
		// Creation des liens entre le compte et les cours
		// INSERT INTO account_course (fk_course,fk_account) VALUES (4, 11);
		for(int i = 0; i < arrayIdCourse.size(); i++)
			nbModifiedRows += callDB.insertData(connection, "INSERT INTO account_course (fk_course,fk_account) " +
															"VALUES ("+ arrayIdCourse.get(i) +"," + resultModel.getValueAt(0, 0) + ");");
		// Envoi du log CREATE-ACCOUNT
		sendLog(LogMgmt.CREATE_ACCOUNT);
		
		return nbModifiedRows;
	}
	
	/**
	 * Modifie les donnees relatives a un compte, ainsi que la liste de cours associes a ce dernier.
	 * @param idAccount
	 * L'id du compte a modifier
	 * @param login
	 * Le login du compte a rajouter
	 * @param password
	 * Le mot de passe du compte a rajouter
	 * @param manager
	 * Le grade du compte (0=Teacher, 1=Manager)
	 * @param firstName
	 * Le prenom du compte a rajouter
	 * @param lastName
	 * Le nom du compte a rajouter
	 * @param tabIdCourse 
	 * Un tableau d'Integer reprenant les id des cours associees au compte
	 * @return
	 * Le nombre de lignes modifiees
	 * @throws InsertionDataException
	 * @throws LoadDriverException 
	 */
	// Requests - Ok !
	public int modifyElement(int idAccount, String login, String password, int manager, String firstName, String lastName, ArrayList<Integer> arrayIdCourse) throws InsertionDataException, EraseDataException, CheckDataException, LoadDriverException
	{
		int nbModifiedRows=0;
		
		// Mise a jour des donnaes du compte
		// UPDATE account SET manager = 0, firstname = 'Aska', lastname = 'Lavista' WHERE id = 11;
		nbModifiedRows += callDB.insertData(connection, "UPDATE account " +
														"SET login = \'" + login + "\', " +
															"password = \'" + password + "\', " +
															"manager = " + manager + ", " +
															"firstname = \'" + firstName + "\', " +
															"lastname = \'" + lastName + "\' " +
														"WHERE id = " + idAccount + ";");
		if(arrayIdCourse != null)
		{
			// Suppression des liens entre le compte et ses cours
			// DELETE FROM account_course WHERE fk_account = " + idAccount + ";
			nbModifiedRows += callDB.insertData(connection, "DELETE FROM account_course WHERE fk_account = " + idAccount + ";");
			
			// Rajout de liens entre le compte et ses (eventuels nouveaux) cours
			// INSERT INTO account_course (fk_account, fk_course) VALUES (11, 5);
			for(int i = 0; i < arrayIdCourse.size(); i++)
				nbModifiedRows += callDB.insertData(connection, "INSERT INTO account_course (fk_account, fk_course) " +
																"VALUES (" + idAccount + ", " + arrayIdCourse.get(i) + ");");
			
			// Envoi du log MODIF-ACCOUNT
			sendLog(LogMgmt.MODIF_ACCOUNT);
		}
		return nbModifiedRows;
	}

	/**
	 * Supprime un compte et ses donnees relatives.
	 * @param idAccount
	 * L'id du compte a supprimer
	 * @return
	 * Le nombre de lignes modifiees
	 * @throws EraseDataException
	 * @throws LoadDriverException 
	 */
	// Requests - Ok !
	public int deleteElement(int idAccount) throws EraseDataException, InsertionDataException, CheckDataException, LoadDriverException
	{
		int nbModifiedRows=0;
		
		// Supprime les liens entre le compte et les cours associees.
		nbModifiedRows += callDB.eraseData(connection, "DELETE FROM account_course WHERE fk_account = " + idAccount + ";");
		
		// Supprime le compte
		nbModifiedRows += callDB.eraseData(connection, "DELETE FROM account WHERE id = " + idAccount + ";");
		
		// Envoi du log DELETE-ACCOUNT
		sendLog(LogMgmt.DELETE_ACCOUNT);
		
		return nbModifiedRows;
	}
	
	private void sendLog(int typeOfLog) throws InsertionDataException, CheckDataException, LoadDriverException
	{
		LogMgmt logMgmt = new LogMgmt(connection);
		logMgmt.createManagerLog(GeneralMethod.getSession().getId(), typeOfLog);
	}
}
